﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.SqlClient;

using RMS.Model;

namespace RMS.Persistence
{
    public sealed class DataManager
    {
        private static readonly DataManager instance = new DataManager();
        private static readonly SqlConnection sqlConnection = new SqlConnection("server=localhost;Persist Security Info=True;uid=sa;pwd=mart1n;Initial Catalog=RMS");

        private DataManager()
        {
            
        }

        public static DataManager Instance { get { return instance; } }

        public List<AcademicProgram> SyncAcademicPrograms()
        {
            List<AcademicProgram> list = new List<AcademicProgram>();

            if (sqlConnection.State == System.Data.ConnectionState.Closed)
                sqlConnection.Open();
            SqlCommand sqlCommand = new SqlCommand("SELECT * FROM AcademicPrograms", sqlConnection);
            SqlDataReader sqlReader = sqlCommand.ExecuteReader();
            if (sqlReader.HasRows)
            {
                while (sqlReader.Read())
                {
                    AcademicProgram academicProgram = new AcademicProgram(Convert.ToInt32(sqlReader["AcademicProgramID"].ToString()), sqlReader["AcademicProgramName"].ToString(), Convert.ToInt32(sqlReader["SemesterCount"].ToString()), (AcademicProgramType)Convert.ToInt32(sqlReader["AcademicProgramType"].ToString()), sqlReader["Description"].ToString());
                    list.Add(academicProgram);
                }
                sqlReader.Close();
                sqlConnection.Close();
            }
            return list;
        }

        public List<Discipline> SyncDisciplineList()
        {
            List<Discipline> list = new List<Discipline>();
            if (sqlConnection.State == System.Data.ConnectionState.Closed)
                sqlConnection.Open();
            SqlCommand sqlCommand = new SqlCommand("SELECT * FROM Disciplines", sqlConnection);
            SqlDataReader sqlReader = sqlCommand.ExecuteReader();
            if (sqlReader.HasRows)
            {
                while (sqlReader.Read())
                {   /*
                    Discipline discipline = new Discipline();
                    discipline.Id = Convert.ToInt32(sqlReader["DisciplineID"]);
                    discipline.Name = sqlReader["DisciplineName"].ToString();
                    discipline.Semester = Convert.ToInt32(sqlReader["Semester"].ToString());
                    list.Add(discipline);
                     * */
                }
                sqlReader.Close();
                sqlConnection.Close();
            }
            return list;
        }

        public List<Discipline> SyncDisciplineListForProgram(int programId)
        {
            List<Discipline> list = new List<Discipline>();
            List<int> listOfDisciplineIds = new List<int>();
            if (sqlConnection.State == System.Data.ConnectionState.Closed)
                sqlConnection.Open();
            SqlCommand sqlCommand = new SqlCommand("SELECT * FROM AcademicProgramDisciplines WHERE AcademicProgramID = " + programId.ToString(), sqlConnection);
            SqlDataReader sqlReader = sqlCommand.ExecuteReader();
            if (sqlReader.HasRows)
            {
                while (sqlReader.Read())
                    listOfDisciplineIds.Add(Convert.ToInt32(sqlReader["DisciplineID"]));
            }
            sqlReader.Close();
            if (listOfDisciplineIds.Count != 0)
            {
                foreach (int disciplineId in listOfDisciplineIds)
                {
                    sqlReader = new SqlCommand("SELECT * FROM Disciplines WHERE DisciplineID = " + disciplineId.ToString(), sqlConnection).ExecuteReader();
                    if (sqlReader.HasRows)
                    {   /*
                        sqlReader.Read();
                        Discipline discipline = new Discipline();
                        discipline.Id = Convert.ToInt32(sqlReader["DisciplineID"]);
                        discipline.Name = sqlReader["DisciplineName"].ToString();
                        discipline.Semester = Convert.ToInt32(sqlReader["Semester"].ToString());
                        list.Add(discipline); */
                    }
                    sqlReader.Close();
                }
            }
            sqlConnection.Close();
            return list;
        }

        public List<HumanResource> SyncAcademicDepartmentHumanResources(int departmentId)
        {
            List<HumanResource> hrList = new List<HumanResource>();
            List<int> hrIdsList = new List<int>();
            if (sqlConnection.State == System.Data.ConnectionState.Closed)
                sqlConnection.Open();
            SqlCommand sqlCommand = new SqlCommand("SELECT MemberID FROM DepartmentMembers WHERE DepartmentID = " + departmentId.ToString(), sqlConnection);
            SqlDataReader sqlReader = sqlCommand.ExecuteReader();
            if (sqlReader.HasRows)
                while (sqlReader.Read())
                    hrIdsList.Add(Convert.ToInt32(sqlReader[0]));
            sqlReader.Close();
            if (hrIdsList.Count != 0)
            {
                foreach (int memberId in hrIdsList)
                {
                    sqlReader = new SqlCommand("SELECT * FROM Users WHERE UserID = " + memberId.ToString(), sqlConnection).ExecuteReader();
                    if (sqlReader.HasRows)
                    {   
                        sqlReader.Read();
                        HumanResource hr = new HumanResource();
                        hr.Id = Convert.ToInt32(sqlReader["UserID"]);
                        hr.Username = sqlReader["Username"].ToString();
                        hr.Authority = (UserType)Convert.ToInt32(sqlReader["Authority"]);
                        hr.FirstName = sqlReader["FirstName"].ToString();
                        hr.LastName = sqlReader["LastName"].ToString();
                        hr.Address = sqlReader["Address"].ToString();
                        hr.PhoneNumber = sqlReader["PhoneNumber"].ToString();
                        hrList.Add(hr);
                    }
                    sqlReader.Close();
                }
            }
            foreach (HumanResource hr in hrList)
            {
                if (hr.Authority == UserType.Teacher)
                {
                    sqlReader = new SqlCommand("SELECT TeacherTitle FROM Teachers WHERE TeacherID = " + hr.Id.ToString(), sqlConnection).ExecuteReader();
                    if (sqlReader.HasRows)
                    {
                        sqlReader.Read();
                        hr.Title = sqlReader["TeacherTitle"].ToString();
                    }
                    sqlReader.Close();
                }
            }
            sqlConnection.Close();
            return hrList;
        }

        public int InsertHumanResource(HumanResource hr, int departmentId)
        {
            SqlCommand sqlCommand;
            int retval = 0;
            if (sqlConnection.State == System.Data.ConnectionState.Closed)
                sqlConnection.Open();
            switch (hr.Authority)
            {
                case UserType.Director:
                    sqlCommand = new SqlCommand("RMS_SP_AddDirector", sqlConnection);
                    sqlCommand.CommandType = System.Data.CommandType.StoredProcedure;
                    sqlCommand.Parameters.AddWithValue("@username", hr.Username);
                    sqlCommand.Parameters.AddWithValue("@firstName", hr.FirstName);
                    sqlCommand.Parameters.AddWithValue("@lastName", hr.LastName);
                    sqlCommand.Parameters.AddWithValue("@address", hr.Address);
                    sqlCommand.Parameters.AddWithValue("@phoneNumber", hr.PhoneNumber);
                    sqlCommand.Parameters.AddWithValue("@departmentId", departmentId);
                    var retparam = sqlCommand.Parameters.Add("@returnCode", System.Data.SqlDbType.Int);
                    retparam.Direction = System.Data.ParameterDirection.ReturnValue;
                    sqlCommand.ExecuteNonQuery();
                    retval = Convert.ToInt32(retparam.Value);
                    if (retval < 0)
                        Console.WriteLine("PROC RMS_SP_AddDirector - RETVAL: " + retval.ToString());
                    break;
                case UserType.Secretary:
                    sqlCommand = new SqlCommand("RMS_SP_AddSecretary", sqlConnection);
                    sqlCommand.CommandType = System.Data.CommandType.StoredProcedure;
                    sqlCommand.Parameters.AddWithValue("@username", hr.Username);
                    sqlCommand.Parameters.AddWithValue("@firstName", hr.FirstName);
                    sqlCommand.Parameters.AddWithValue("@lastName", hr.LastName);
                    sqlCommand.Parameters.AddWithValue("@address", hr.Address);
                    sqlCommand.Parameters.AddWithValue("@phoneNumber", hr.PhoneNumber);
                    sqlCommand.Parameters.AddWithValue("@departmentId", departmentId);
                    retparam = sqlCommand.Parameters.Add("@returnCode", System.Data.SqlDbType.Int);
                    retparam.Direction = System.Data.ParameterDirection.ReturnValue;
                    sqlCommand.ExecuteNonQuery();
                    retval = Convert.ToInt32(retparam.Value);
                    if (retval < 0)
                        Console.WriteLine("PROC RMS_SP_AddSecretary - RETVAL: " + retval.ToString());
                    break;
                case UserType.Teacher:
                    sqlCommand = new SqlCommand("RMS_SP_AddTeacher", sqlConnection);
                    sqlCommand.CommandType = System.Data.CommandType.StoredProcedure;
                    sqlCommand.Parameters.AddWithValue("@username", hr.Username);
                    sqlCommand.Parameters.AddWithValue("@title", hr.Title);
                    sqlCommand.Parameters.AddWithValue("@firstName", hr.FirstName);
                    sqlCommand.Parameters.AddWithValue("@lastName", hr.LastName);
                    sqlCommand.Parameters.AddWithValue("@address", hr.Address);
                    sqlCommand.Parameters.AddWithValue("@phoneNumber", hr.PhoneNumber);
                    sqlCommand.Parameters.AddWithValue("@departmentId", departmentId);
                    retparam = sqlCommand.Parameters.Add("@returnCode", System.Data.SqlDbType.Int);
                    retparam.Direction = System.Data.ParameterDirection.ReturnValue;
                    sqlCommand.ExecuteNonQuery();
                    retval = Convert.ToInt32(retparam.Value);
                    if (retval < 0)
                        Console.WriteLine("EXEC PROC RMS_SP_AddTeacher - RETVAL: " + retval.ToString());
                    break;
                default:
                    break;
            }
            sqlConnection.Close();
            return retval;
        }

        public void RemoveHumanResource(HumanResource hr)
        {
            SqlCommand sqlCommand;
            if (sqlConnection.State == System.Data.ConnectionState.Closed)
                sqlConnection.Open();
            switch (hr.Authority)
            {
                case UserType.Director:
                    sqlCommand = new SqlCommand("RMS_SP_RemoveDirector", sqlConnection);
                    sqlCommand.CommandType = System.Data.CommandType.StoredProcedure;
                    sqlCommand.Parameters.AddWithValue("@id", hr.Id);
                    sqlCommand.ExecuteNonQuery();
                    break;
                case UserType.Secretary:
                    sqlCommand = new SqlCommand("RMS_SP_RemoveSecretary", sqlConnection);
                    sqlCommand.CommandType = System.Data.CommandType.StoredProcedure;
                    sqlCommand.Parameters.AddWithValue("@id", hr.Id);
                    sqlCommand.ExecuteNonQuery();
                    break;
                case UserType.Teacher:
                    sqlCommand = new SqlCommand("RMS_SP_RemoveTeacher", sqlConnection);
                    sqlCommand.CommandType = System.Data.CommandType.StoredProcedure;
                    sqlCommand.Parameters.AddWithValue("@id", hr.Id);
                    sqlCommand.ExecuteNonQuery();
                    break;
                default:
                    break;
            }
            sqlConnection.Close();
        }

        public void UpdateHumanResource(HumanResource hr)
        {
            SqlCommand sqlCommand;
            if (sqlConnection.State == System.Data.ConnectionState.Closed)
                sqlConnection.Open();
            switch (hr.Authority)
            {
                case UserType.Director:
                    sqlCommand = new SqlCommand("RMS_SP_UpdateDirector", sqlConnection);
                    sqlCommand.CommandType = System.Data.CommandType.StoredProcedure;
                    sqlCommand.Parameters.AddWithValue("@id", hr.Id);
                    sqlCommand.Parameters.AddWithValue("@username", hr.Username);
                    sqlCommand.Parameters.AddWithValue("@firstName", hr.FirstName);
                    sqlCommand.Parameters.AddWithValue("@lastName", hr.LastName);
                    sqlCommand.Parameters.AddWithValue("@address", hr.Address);
                    sqlCommand.Parameters.AddWithValue("@phoneNumber", hr.PhoneNumber);
                    sqlCommand.ExecuteNonQuery();
                    break;
                case UserType.Secretary:
                    sqlCommand = new SqlCommand("RMS_SP_UpdateSecretary", sqlConnection);
                    sqlCommand.CommandType = System.Data.CommandType.StoredProcedure;
                    sqlCommand.Parameters.AddWithValue("@id", hr.Id);
                    sqlCommand.Parameters.AddWithValue("@username", hr.Username);
                    sqlCommand.Parameters.AddWithValue("@firstName", hr.FirstName);
                    sqlCommand.Parameters.AddWithValue("@lastName", hr.LastName);
                    sqlCommand.Parameters.AddWithValue("@address", hr.Address);
                    sqlCommand.Parameters.AddWithValue("@phoneNumber", hr.PhoneNumber);
                    sqlCommand.ExecuteNonQuery();
                    break;
                case UserType.Teacher:
                    sqlCommand = new SqlCommand("RMS_SP_UpdateTeacher", sqlConnection);
                    sqlCommand.CommandType = System.Data.CommandType.StoredProcedure;
                    sqlCommand.Parameters.AddWithValue("@id", hr.Id);
                    sqlCommand.Parameters.AddWithValue("@username", hr.Username);
                    sqlCommand.Parameters.AddWithValue("@title", hr.Title);
                    sqlCommand.Parameters.AddWithValue("@firstName", hr.FirstName);
                    sqlCommand.Parameters.AddWithValue("@lastName", hr.LastName);
                    sqlCommand.Parameters.AddWithValue("@address", hr.Address);
                    sqlCommand.Parameters.AddWithValue("@phoneNumber", hr.PhoneNumber);
                    sqlCommand.ExecuteNonQuery();
                    break;
                default:
                    break;
            }
            sqlConnection.Close();
        }

        public Teacher SyncTeacherWithDatabase(int teacherId)
        {
            if (sqlConnection.State == System.Data.ConnectionState.Closed)
                sqlConnection.Open();
            SqlCommand sqlCommand = new SqlCommand("SELECT * FROM Users WHERE UserID = " + teacherId.ToString(), sqlConnection);
            SqlDataReader sqlReader = sqlCommand.ExecuteReader();
            if (sqlReader.HasRows == false)
            {
                sqlReader.Close();
                sqlConnection.Close();
                return null;
            }
            sqlReader.Read();
            Teacher teacher = new Teacher();
            teacher.Id = teacherId;
            teacher.Username = sqlReader["Username"].ToString();
            teacher.FirstName = sqlReader["FirstName"].ToString();
            teacher.LastName = sqlReader["LastName"].ToString();
            sqlReader.Close();
            sqlCommand = new SqlCommand("SELECT * FROM AcademicDuties WHERE TeacherID = " + teacherId.ToString(), sqlConnection);
            sqlReader = sqlCommand.ExecuteReader();
            if (sqlReader.HasRows == false)
            {
                sqlReader.Close();
                sqlConnection.Close();
                return null;
            }
           
            SqlConnection sqlConnection2 = new SqlConnection("server=localhost;Persist Security Info=True;uid=sa;pwd=mart1n;Initial Catalog=RMS");
            if (sqlConnection2.State == System.Data.ConnectionState.Closed)
                sqlConnection2.Open();
                
            while (sqlReader.Read())
            {
                int dutyId = Convert.ToInt32(sqlReader["DutyID"].ToString());
                int academicProgramId = Convert.ToInt32(sqlReader["AcademicProgramID"].ToString());
                AcademicDuty duty = new AcademicDuty(dutyId, teacherId, academicProgramId);
                // TODO: switch to 1 connection only
                SqlCommand sqlCommand2 = new SqlCommand("SELECT * FROM TeacherDutyDisciplines WHERE DutyID = " + dutyId.ToString(), sqlConnection2);
                SqlDataReader sqlReader2 = sqlCommand2.ExecuteReader();
                if (sqlReader2.HasRows == false)
                {
                    sqlReader2.Close();
                    continue;
                }
                while (sqlReader2.Read())
                {
                    Discipline discipline = new Discipline(Convert.ToInt32(sqlReader2["DisciplineID"].ToString()), sqlReader2["DisciplineName"].ToString(), Convert.ToInt32(sqlReader2["Semester"].ToString()), sqlReader2["Description"].ToString());
                    Tuple<Discipline, int, int, int> tuple = new Tuple<Discipline, int, int, int>(discipline, Convert.ToInt32(sqlReader2["CourseHours"].ToString()), Convert.ToInt32(sqlReader2["SeminarHours"].ToString()), Convert.ToInt32(sqlReader2["LabHours"].ToString()));
                    duty.DisciplineScheduling.Add(tuple);                    
                }
                sqlReader2.Close();
                teacher.Duties.Add(duty);
            }
            sqlReader.Close();
            sqlConnection2.Close();
            sqlConnection.Close();
            return teacher;
        }

        public List<AcademicProgram> SyncTeacherAcademicPrograms(int teacherId)
        {
            List<AcademicProgram> listPrograms = new List<AcademicProgram>();
            if (sqlConnection.State == System.Data.ConnectionState.Closed)
                sqlConnection.Open();
            SqlCommand sqlCommand = new SqlCommand("SELECT DISTINCT AcademicProgramID FROM AcademicDuties WHERE TeacherID = " + teacherId.ToString(), sqlConnection);
            SqlDataReader sqlReader = sqlCommand.ExecuteReader();
            if (sqlReader.HasRows == false)
            {
                sqlReader.Close();
                sqlConnection.Close();
                return null;
            }
            SqlConnection sqlConnection2 = new SqlConnection("server=localhost;Persist Security Info=True;uid=sa;pwd=mart1n;Initial Catalog=RMS");
            //TODO : Switch to 1 connection
            if (sqlConnection2.State == System.Data.ConnectionState.Closed)
                sqlConnection2.Open();

            while (sqlReader.Read())
            {
                SqlCommand sqlCommand2 = new SqlCommand("SELECT * FROM AcademicPrograms WHERE AcademicProgramID = " + sqlReader["AcademicProgramID"].ToString(), sqlConnection2);
                SqlDataReader sqlReader2 = sqlCommand2.ExecuteReader();
                if (sqlReader2.HasRows == false)
                {
                    sqlReader2.Close();
                    continue;
                }
                sqlReader2.Read();
                AcademicProgram ap = new AcademicProgram(1, "", 0, AcademicProgramType.Undergrad);
                ap.Id = Convert.ToInt32(sqlReader2["AcademicProgramID"].ToString());
                ap.Name = sqlReader2["AcademicProgramName"].ToString();
                ap.SemesterCount = Convert.ToInt32(sqlReader2["SemesterCount"].ToString());
                ap.Type = (AcademicProgramType)Convert.ToInt32(sqlReader2["AcademicProgramType"].ToString());
                listPrograms.Add(ap);
                sqlReader2.Close();
            }
            sqlReader.Close();
            sqlConnection2.Close();
            sqlConnection.Close();
            return listPrograms;
        }

        public int InsertAcademicProgram(AcademicProgram program)
        {
            SqlCommand sqlCommand;
            int retval = 0;
            if (sqlConnection.State == System.Data.ConnectionState.Closed)
                sqlConnection.Open();
            sqlCommand = new SqlCommand("RMS_SP_AddProgram", sqlConnection);
            sqlCommand.CommandType = System.Data.CommandType.StoredProcedure;
            sqlCommand.Parameters.AddWithValue("@name", program.Name);
            sqlCommand.Parameters.AddWithValue("@description", program.Description);
            sqlCommand.Parameters.AddWithValue("@semesterCount", program.SemesterCount);
            sqlCommand.Parameters.AddWithValue("@type", Convert.ToInt32(program.Type));
            var retparam = sqlCommand.Parameters.Add("@returnCode", System.Data.SqlDbType.Int);
            retparam.Direction = System.Data.ParameterDirection.ReturnValue;
            sqlCommand.ExecuteNonQuery();
            retval = Convert.ToInt32(retparam.Value);
            if (retval < 0)
                Console.WriteLine("PROC RMS_SP_AddProgram - RETVAL: " + retval.ToString());
            sqlConnection.Close();
            return retval;
        }

        public void RemoveAcademicProgram(AcademicProgram program)
        {
            if (sqlConnection.State == System.Data.ConnectionState.Closed)
                sqlConnection.Open();
            SqlCommand sqlCommand = new SqlCommand("RMS_SP_RemoveProgram", sqlConnection);
            sqlCommand.CommandType = System.Data.CommandType.StoredProcedure;
            sqlCommand.Parameters.AddWithValue("@id", program.Id);
            sqlCommand.ExecuteNonQuery();
            sqlConnection.Close();
        }

        public void UpdateAcademicProgram(AcademicProgram program)
        {
            if (sqlConnection.State == System.Data.ConnectionState.Closed)
                sqlConnection.Open();
            SqlCommand sqlCommand = new SqlCommand("RMS_SP_UpdateProgram", sqlConnection);
            sqlCommand.CommandType = System.Data.CommandType.StoredProcedure;
            sqlCommand.Parameters.AddWithValue("@id", program.Id);
            sqlCommand.Parameters.AddWithValue("@name", program.Name);
            sqlCommand.Parameters.AddWithValue("@description", program.Description);
            sqlCommand.Parameters.AddWithValue("@semesterCount", program.SemesterCount);
            sqlCommand.Parameters.AddWithValue("@type", Convert.ToInt32(program.Type));
            var retparam = sqlCommand.Parameters.Add("@returnCode", System.Data.SqlDbType.Int);
            retparam.Direction = System.Data.ParameterDirection.ReturnValue;
            sqlCommand.ExecuteNonQuery();
            sqlConnection.Close();
        }

        public int Login(string p1, string p2)
        {
            if (sqlConnection.State == System.Data.ConnectionState.Closed)
                sqlConnection.Open();
            string sqlCommandString = string.Format("SELECT Authority FROM Users WHERE Username = '{0}' AND Password = '{1}'", p1, p2);
            SqlCommand sqlCommand = new SqlCommand(sqlCommandString, sqlConnection);
            object retval = sqlCommand.ExecuteScalar();
            if (retval == null) return -1;
            return Convert.ToInt32(retval);
        }

        public int GetID(string p1)
        {
            if (sqlConnection.State == System.Data.ConnectionState.Closed)
                sqlConnection.Open();
            string sqlCommandString = string.Format("SELECT UserID FROM Users WHERE Username = '{0}'", p1);
            SqlCommand sqlCommand = new SqlCommand(sqlCommandString, sqlConnection);
            object retval = sqlCommand.ExecuteScalar();
            if (retval == null) return -1;
            return Convert.ToInt32(retval);
        }

        public int GetDepartmentID(int id)
        {
            if (sqlConnection.State == System.Data.ConnectionState.Closed)
                sqlConnection.Open();
            string sqlCommandString = string.Format("SELECT DepartmentID FROM Departments WHERE DirectorID = '{0}'", id);
            SqlCommand sqlCommand = new SqlCommand(sqlCommandString, sqlConnection);
            object retval = sqlCommand.ExecuteScalar();
            if (retval == null) return -1;
            return Convert.ToInt32(retval);
        }

        public AcademicDepartment SyncAcademicDepartment(int id)
        {
            AcademicDepartment department = new AcademicDepartment(id, "", "");
            if (sqlConnection.State == System.Data.ConnectionState.Closed)
                sqlConnection.Open();
            string sqlCommandString = string.Format("SELECT * FROM Departments WHERE DepartmentID = '{0}'", id);
            SqlCommand sqlCommand = new SqlCommand(sqlCommandString, sqlConnection);
            SqlDataReader sqlReader = sqlCommand.ExecuteReader();
            if (sqlReader.HasRows == false)
            {
                sqlReader.Close();
                sqlConnection.Close();
                return null;
            }
            sqlReader.Read();
            department.Name = sqlReader["DepartmentName"].ToString();
            department.Description = sqlReader["Description"].ToString();
            sqlReader.Close();
            sqlConnection.Close();
            return department;
        }

        public List<ResearchProject> SyncResearchProjects()
        {
            List<ResearchProject> projectList = new List<ResearchProject>();
            if (sqlConnection.State == System.Data.ConnectionState.Closed)
                sqlConnection.Open();
            SqlCommand sqlCommand = new SqlCommand("SELECT * FROM ResearchProjects", sqlConnection);
            SqlDataReader sqlReader = sqlCommand.ExecuteReader();
            if (sqlReader.HasRows == false)
            {
                sqlReader.Close();
                sqlConnection.Close();
                return projectList;
            }
            while (sqlReader.Read())
            {
                ResearchProject project = new ResearchProject(0, "", "");
                project.Id = Convert.ToInt32(sqlReader["ProjectID"].ToString());
                project.Name = sqlReader["ProjectName"].ToString();
                project.Description = sqlReader["Description"].ToString();
                project.Team = new ResearchTeam(Convert.ToInt32(sqlReader["TeamID"].ToString()), "", "");
                projectList.Add(project);
            }
            sqlReader.Close();
            foreach (ResearchProject project in projectList)
            {
                sqlCommand = new SqlCommand("SELECT * FROM ResearchTeams WHERE TeamID = " + project.Team.Id.ToString(), sqlConnection);
                sqlReader = sqlCommand.ExecuteReader();
                if (sqlReader.HasRows)
                {
                    sqlReader.Read();
                    project.Team.Name = sqlReader["TeamName"].ToString();
                    project.Team.Description = sqlReader["Description"].ToString();
                }
                sqlReader.Close();
                sqlCommand = new SqlCommand("SELECT * FROM TeamMembers WHERE TeamID = " + project.Team.Id.ToString(), sqlConnection);
                sqlReader = sqlCommand.ExecuteReader();
                if (sqlReader.HasRows)
                {
                    while (sqlReader.Read())
                    {
                        ResearchTeamMember member = new ResearchTeamMember(0, "", 0);
                        member.Id = Convert.ToInt32(sqlReader["MemberID"].ToString());
                        member.Name = sqlReader["FirstName"].ToString() + " " + sqlReader["LastName"].ToString();
                        member.HoursCount = Convert.ToInt32(sqlReader["Hours"].ToString());
                        project.Team.Members.Add(member);
                    }
                }
                sqlReader.Close();
                sqlCommand = new SqlCommand("SELECT * FROM ResearchPhases WHERE ProjectID = " + project.Id.ToString(), sqlConnection);
                sqlReader = sqlCommand.ExecuteReader();
                if (sqlReader.HasRows)
                {
                    while (sqlReader.Read())
                    {
                        ResearchPhase phase = new ResearchPhase(0, "", "");
                        phase.Id = Convert.ToInt32(sqlReader["PhaseID"].ToString());
                        phase.Name = sqlReader["PhaseName"].ToString();
                        phase.Description = sqlReader["Description"].ToString();
                        project.Phases.Add(phase);
                    }
                }
                sqlReader.Close();
            }
            foreach (ResearchProject project in projectList)
            {
                foreach (ResearchPhase phase in project.Phases)
                {
                    sqlCommand = new SqlCommand("SELECT * FROM ResearchActivities WHERE PhaseID = " + phase.Id.ToString(), sqlConnection);
                    sqlReader = sqlCommand.ExecuteReader();
                    if (sqlReader.HasRows)
                    {
                        while (sqlReader.Read())
                        {
                            ResearchActivity activity = new ResearchActivity(0, 0, "", "");
                            activity.Id = Convert.ToInt32(sqlReader["ActivityID"].ToString());
                            activity.Name = sqlReader["ActivityName"].ToString();
                            activity.Description = sqlReader["Description"].ToString();
                            activity.Order = Convert.ToInt32(sqlReader["Number"].ToString());
                            activity.Budget.Id = Convert.ToInt32(sqlReader["BudgetID"].ToString());
                            phase.Activities.Add(activity.Order, activity);
                        }
                    }
                    sqlReader.Close();
                    foreach (ResearchActivity activity in phase.Activities.Values)
                    {
                        sqlCommand = new SqlCommand("SELECT * FROM Expenses WHERE BudgetID = " + activity.Budget.Id.ToString(), sqlConnection);
                        sqlReader = sqlCommand.ExecuteReader();
                        if (sqlReader.HasRows)
                        {
                            while (sqlReader.Read())
                            {
                                Expense expense = new Expense(0, 0, "", ExpenseType.Logistics);
                                expense.Id = Convert.ToInt32(sqlReader["ExpenseID"].ToString());
                                expense.Description = sqlReader["Description"].ToString();
                                expense.Value = Convert.ToDouble(sqlReader["ExpenseValue"].ToString());
                                expense.Type = (ExpenseType)Convert.ToInt32(sqlReader["ExpenseType"].ToString());
                                activity.Budget.Items.Add(expense);
                            }
                        }
                        sqlReader.Close();
                    }
                }
            }
            sqlConnection.Close();
            return projectList;
        }

        public List<Student> SyncStudentList()
        {
            List<Student> studentList = new List<Student>();
            if (sqlConnection.State == System.Data.ConnectionState.Closed)
                sqlConnection.Open();
            SqlCommand sqlCommand = new SqlCommand("SELECT * FROM StudentsInfo", sqlConnection);
            SqlDataReader sqlReader = sqlCommand.ExecuteReader();
            if (sqlReader.HasRows)
            {
                while (sqlReader.Read())
                {
                    Student student = new Student();
                    student.Id = Convert.ToInt32(sqlReader["UserID"].ToString());
                    student.FirstName = sqlReader["FirstName"].ToString();
                    student.LastName = sqlReader["LastName"].ToString();
                    studentList.Add(student);
                }
            }
            sqlReader.Close();
            sqlConnection.Close();
            return studentList;
        }

        public List<StudentResearchGroup> SyncStudentResearchGroups()
        {
            List<StudentResearchGroup> groupList = new List<StudentResearchGroup>();
            if (sqlConnection.State == System.Data.ConnectionState.Closed)
                sqlConnection.Open();
            SqlCommand sqlCommand = new SqlCommand("SELECT * FROM StudentResearchGroups", sqlConnection);
            SqlDataReader sqlReader = sqlCommand.ExecuteReader();
            if (sqlReader.HasRows == false)
            {
                sqlReader.Close();
                sqlConnection.Close();
                return groupList;
            }
            while (sqlReader.Read())
            {
                StudentResearchGroup group = new StudentResearchGroup(0, 0, "", "");
                group.Id = Convert.ToInt32(sqlReader["GroupID"].ToString());
                group.ProgramId = Convert.ToInt32(sqlReader["AcademicProgramID"].ToString());
                group.Name = sqlReader["GroupName"].ToString();
                group.Description = sqlReader["Description"].ToString();
                groupList.Add(group);
            }
            sqlReader.Close();
            foreach (StudentResearchGroup group in groupList)
            {
                sqlCommand = new SqlCommand("SELECT * FROM ResearchGroupStudents WHERE GroupID = " + group.Id.ToString(), sqlConnection);
                sqlReader = sqlCommand.ExecuteReader();
                if (sqlReader.HasRows)
                {
                    while (sqlReader.Read())
                    {
                        Student student = new Student();
                        student.GroupId = group.Id;
                        student.Id = Convert.ToInt32(sqlReader["UserID"].ToString());
                        student.FirstName = sqlReader["FirstName"].ToString();
                        student.LastName = sqlReader["LastName"].ToString();
                        group.Students.Add(student);
                    }
                }
                sqlReader.Close();
            }
            return groupList;
        }

        public int AddStudentGroup(StudentResearchGroup group)
        {
            SqlCommand sqlCommand;
            int retval = 0;
            if (sqlConnection.State == System.Data.ConnectionState.Closed)
                sqlConnection.Open();
            sqlCommand = new SqlCommand("RMS_SP_AddStudentResearchGroup", sqlConnection);
            sqlCommand.CommandType = System.Data.CommandType.StoredProcedure;
            sqlCommand.Parameters.AddWithValue("@name", group.Name);
            sqlCommand.Parameters.AddWithValue("@programId", group.ProgramId);
            sqlCommand.Parameters.AddWithValue("@description", group.Description);
            var retparam = sqlCommand.Parameters.Add("@returnCode", System.Data.SqlDbType.Int);
            retparam.Direction = System.Data.ParameterDirection.ReturnValue;
            sqlCommand.ExecuteNonQuery();
            retval = Convert.ToInt32(retparam.Value);
            if (retval < 0)
                Console.WriteLine("PROC RMS_SP_AddStudentResearchGroup - RETVAL: " + retval.ToString());
            sqlConnection.Close();
            return retval;
        }

        public int RemoveStudentGroup(StudentResearchGroup group)
        {
            SqlCommand sqlCommand;
            if (sqlConnection.State == System.Data.ConnectionState.Closed)
                sqlConnection.Open();
            sqlCommand = new SqlCommand("RMS_SP_RemoveStudentResearchGroup", sqlConnection);
            sqlCommand.CommandType = System.Data.CommandType.StoredProcedure;
            sqlCommand.Parameters.AddWithValue("@id", group.Id);
            var retparam = sqlCommand.Parameters.Add("@returnCode", System.Data.SqlDbType.Int);
            retparam.Direction = System.Data.ParameterDirection.ReturnValue;
            sqlCommand.ExecuteNonQuery();
            int retval = Convert.ToInt32(retparam.Value);
            if (retval < 0)
                Console.WriteLine("PROC RMS_SP_RemoveStudentResearchGroup - RETVAL: " + retval.ToString());
            sqlConnection.Close();
            return retval;
        }

        public void UpdateResearchGroup(StudentResearchGroup group)
        {
            if (sqlConnection.State == System.Data.ConnectionState.Closed)
                sqlConnection.Open();
            SqlCommand sqlCommand = new SqlCommand("RMS_SP_UpdateStudentResearchGroup", sqlConnection);
            sqlCommand.CommandType = System.Data.CommandType.StoredProcedure;
            sqlCommand.Parameters.AddWithValue("@id", group.Id);
            sqlCommand.Parameters.AddWithValue("@name", group.Name);
            sqlCommand.Parameters.AddWithValue("@programId", group.ProgramId);
            sqlCommand.Parameters.AddWithValue("@description", group.Description);
            var retparam = sqlCommand.Parameters.Add("@returnCode", System.Data.SqlDbType.Int);
            retparam.Direction = System.Data.ParameterDirection.ReturnValue;
            sqlCommand.ExecuteNonQuery();
            sqlConnection.Close();
        }

        public int AddResearchProject(ResearchProject project)
        {
            SqlCommand sqlCommand;
            int retval = 0;
            if (sqlConnection.State == System.Data.ConnectionState.Closed)
                sqlConnection.Open();
            sqlCommand = new SqlCommand("RMS_SP_AddResearchProject", sqlConnection);
            sqlCommand.CommandType = System.Data.CommandType.StoredProcedure;
            sqlCommand.Parameters.AddWithValue("@name", project.Name);
            sqlCommand.Parameters.AddWithValue("@description", project.Description);
            sqlCommand.Parameters.AddWithValue("@teamName", project.Team.Name);
            sqlCommand.Parameters.AddWithValue("@teamDescription", project.Team.Description);
            var retparam = sqlCommand.Parameters.Add("@returnCode", System.Data.SqlDbType.Int);
            retparam.Direction = System.Data.ParameterDirection.ReturnValue;
            sqlCommand.ExecuteNonQuery();
            retval = Convert.ToInt32(retparam.Value);
            if (retval < 0)
                Console.WriteLine("PROC RMS_SP_AddResearchProject - RETVAL: " + retval.ToString());
            sqlConnection.Close();
            return retval;
        }

        public int GetTeamID(int p)
        {
            if (sqlConnection.State == System.Data.ConnectionState.Closed)
                sqlConnection.Open();
            SqlCommand sqlCommand = new SqlCommand("SELECT TeamID FROM ResearchProjects WHERE ProjectID = " + p.ToString(), sqlConnection);
            int id = Convert.ToInt32(sqlCommand.ExecuteScalar());
            return id;
        }

        public int RemoveResearchProject(ResearchProject project)
        {
            SqlCommand sqlCommand;
            if (sqlConnection.State == System.Data.ConnectionState.Closed)
                sqlConnection.Open();
            sqlCommand = new SqlCommand("RMS_SP_RemoveResearchProject", sqlConnection);
            sqlCommand.CommandType = System.Data.CommandType.StoredProcedure;
            sqlCommand.Parameters.AddWithValue("@id", project.Id);
            var retparam = sqlCommand.Parameters.Add("@returnCode", System.Data.SqlDbType.Int);
            retparam.Direction = System.Data.ParameterDirection.ReturnValue;
            sqlCommand.ExecuteNonQuery();
            int retval = Convert.ToInt32(retparam.Value);
            if (retval < 0)
                Console.WriteLine("PROC RMS_SP_RemoveResearchProject - RETVAL: " + retval.ToString());
            sqlConnection.Close();
            return retval;
        }

        public void UpdateResearchProject(ResearchProject project)
        {
            if (sqlConnection.State == System.Data.ConnectionState.Closed)
                sqlConnection.Open();
            SqlCommand sqlCommand = new SqlCommand("RMS_SP_UpdateResearchProject", sqlConnection);
            sqlCommand.CommandType = System.Data.CommandType.StoredProcedure;
            sqlCommand.Parameters.AddWithValue("@id", project.Id);
            sqlCommand.Parameters.AddWithValue("@name", project.Name);
            sqlCommand.Parameters.AddWithValue("@description", project.Description);
            sqlCommand.Parameters.AddWithValue("@teamId", project.Team.Id);
            sqlCommand.Parameters.AddWithValue("@teamName", project.Team.Name);
            sqlCommand.Parameters.AddWithValue("@teamDescription", project.Team.Description);
            sqlCommand.ExecuteNonQuery();
            sqlConnection.Close();
        }
    }
}
